package com.test.excel;

/**
 ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** **
 * Description: 
 * Author: Heller (HellerSong@outlook.com)
 * CreationTime: May 17, 2015
 ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** **
 **/

import java.io.*;
import java.lang.reflect.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.text.SimpleDateFormat;

import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CreationHelper;

/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息，注明出处！
 * 
 * @author leno
 * @version v1.0
 * @param <T>
 *            应用泛型，代表任意一个符合javabean风格的类
 *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 *            byte[]表jpg格式的图片数据
 */
public class ExportExcel<T> {
	
	private String sheetName = "导出内容";

	public void exportExcel(Collection<T> dataset, OutputStream out) {
		exportExcel(sheetName, null, dataset, out, "yyyy-MM-dd");
	}

	public void exportExcel(String[] headers, Collection<T> dataset,
			OutputStream out) {
		exportExcel(sheetName, headers, dataset, out, "yyyy-MM-dd");
	}

	public void exportExcel(String[] headers, Collection<T> dataset,
			OutputStream out, String pattern) {
		exportExcel(sheetName, headers, dataset, out, pattern);
	}

	/**
	 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 */
	public void exportExcel(String title, String[] headers,
			Collection<T> dataset, OutputStream out, String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 生成超链接创造
		CreationHelper createHelper = workbook.getCreationHelper();
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);

		// 生成样式，标题使用
		HSSFCellStyle styleHead = workbook.createCellStyle();
		// 设置这些样式
		styleHead.setFillForegroundColor(HSSFColor.BLACK.index);
		styleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		styleHead.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		styleHead.setBottomBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleHead.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		styleHead.setLeftBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleHead.setBorderRight(HSSFCellStyle.BORDER_THIN);
		styleHead.setRightBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleHead.setBorderTop(HSSFCellStyle.BORDER_THIN);
		styleHead.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体，标题使用
		HSSFFont fontHead = workbook.createFont();
		fontHead.setFontName("微软雅黑");
		fontHead.setColor(HSSFColor.WHITE.index);
		fontHead.setFontHeightInPoints((short) 10);
		fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		styleHead.setFont(fontHead);
		
		// 生成并设置另一个样式，内容体使用
		HSSFCellStyle styleBody = workbook.createCellStyle();
		styleBody.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		styleBody.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		styleBody.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		styleBody.setBottomBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleBody.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		styleBody.setLeftBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleBody.setBorderRight(HSSFCellStyle.BORDER_THIN);
		styleBody.setRightBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleBody.setBorderTop(HSSFCellStyle.BORDER_THIN);
		styleBody.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index);
		styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体，内容体使用
		HSSFFont fontBody = workbook.createFont();
		fontBody.setFontName("微软雅黑");
		fontBody.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		styleBody.setFont(fontBody);

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(styleHead);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}

		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值
			Field[] fields = t.getClass().getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				String fieldName = field.getName();
				String getMethodName = "get"
						+ fieldName.substring(0, 1).toUpperCase()
						+ fieldName.substring(1);
				// 只处理想要导出的列
				if (fieldName == "userName" || fieldName == "userTel"
						|| fieldName == "bookCase" || fieldName == "bookTitle"
						|| fieldName == "cardPhoto"
						|| fieldName == "cardPhoto2" || fieldName == "lawPhoto"
						|| fieldName == "bookStatus"
						|| fieldName == "bookResultText"
						|| fieldName == "bookComments") {

					HSSFCell cell = row.createCell(i);
					cell.setCellStyle(styleBody);
					try {
						Class<?> tCls = t.getClass();
						Method getMethod = tCls.getMethod(getMethodName,
								new Class[] {});
						Object value = getMethod.invoke(t, new Object[] {});
						// 判断值的类型后进行强制类型转换
						String textValue = null;

						if (fieldName == "cardPhoto") {
							textValue = "E:\\" + "CardPhoto.jpg";
							org.apache.poi.ss.usermodel.Hyperlink hyperlink = createHelper
									.createHyperlink(Hyperlink.LINK_FILE);
							hyperlink.setAddress(textValue);

							cell.setHyperlink(hyperlink);

						} else if (value instanceof Date) {
							Date date = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat(pattern);
							textValue = sdf.format(date);
						} else {
							textValue = value.toString();
						}
						// 利用正则表达式判断textValue是否全部由数字组成
						if (textValue != null) {
							Pattern p = Pattern.compile("^//d+(//.//d+)?$");
							Matcher matcher = p.matcher(textValue);
							if (matcher.matches()) {
								// 是数字当作double处理
								cell.setCellValue(Double.parseDouble(textValue));
							} else {
								HSSFRichTextString richString = new HSSFRichTextString(
										textValue);
								HSSFFont font3 = workbook.createFont();
								font3.setColor(HSSFColor.BLUE.index);
								richString.applyFont(font3);
								cell.setCellValue(richString);
							}
						}
					} catch (SecurityException e) {
						e.printStackTrace();
					} catch (NoSuchMethodException e) {
						e.printStackTrace();
					} catch (IllegalArgumentException e) {
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						e.printStackTrace();
					} finally {
						// 清理资源
					}
				}

			}

		}
		
		try {
			workbook.write(out);
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
}
